<!DOCTYPE html>
<html>

<head>
    <meta charset="UTF-8" />
    <title>Hello World!</title>
    <link rel='stylesheet' href='./luckysheet/plugins/css/pluginsCss.css' />
    <link rel='stylesheet' href='./luckysheet/plugins/plugins.css' />
    <link rel='stylesheet' href='./luckysheet/css/luckysheet.css' />
    <link rel='stylesheet' href='./luckysheet/assets/iconfont/iconfont.css' />
    <script src="./luckysheet/plugins/js/plugin.js"></script>
    <script src="./luckysheet/luckysheet.umd.js"></script>
    <script src="./polyfill.js"></script>
    <script src="./exceljs.js"></script>
    <script src="./xlsx.full.min.js"></script>
    <script src="./fileSaver.js"></script>
    <script>
        $(function () {
            //Configuration item
            var options = {
                container: 'luckysheet', //luckysheet is the container id
                showinfobar: false,
                lang:"zh"
            }
            luckysheet.create(options)
        });
    </script>
</head>

<body>
    <div id="lucky-mask-demo"
        style="position: absolute;z-index: 1000000;left: 0px;top: 0px;bottom: 0px;right: 0px; background: rgba(255, 255, 255, 0.8); font-size: 40px;align-items:center;justify-content: center;display: none;">
        Downloading</div>
    <p> 
        <input style="font-size:16px;display:none;" type="file" id="Luckyexcel-demo-file"
            name="Luckyexcel-demo-file" change="demoHandler" />
            
            <!-- <select
            style="height: 27px;top: -2px;position: relative;" id="Luckyexcel-select-demo">
            <option value="">select a demo</option>
            <option value="http://192.168.1.182:8180/uploadFile/2020/10/20/1603176815594.xlsx">Money Manager.xlsx
            </option>
            <option value="https://minio.cnbabylon.com/public/luckysheet/Activity%20costs%20tracker.xlsx">Activity costs
                tracker.xlsx</option>
            <option value="https://minio.cnbabylon.com/public/luckysheet/House%20cleaning%20checklist.xlsx">House
                cleaning checklist.xlsx</option>
            <option value="https://minio.cnbabylon.com/public/luckysheet/Student%20assignment%20planner.xlsx">Student
                assignment planner.xlsx</option>
            <option value="https://minio.cnbabylon.com/public/luckysheet/Credit%20card%20tracker.xlsx">Credit card
                tracker.xlsx</option>
            <option value="https://minio.cnbabylon.com/public/luckysheet/Blue%20timesheet.xlsx">Blue timesheet.xlsx
            </option>
            <option value="https://minio.cnbabylon.com/public/luckysheet/Student%20calendar%20%28Mon%29.xlsx">Student
                calendar (Mon).xlsx</option>
            <option value="https://minio.cnbabylon.com/public/luckysheet/Blue%20mileage%20and%20expense%20report.xlsx">
                Blue mileage and expense report.xlsx</option>
        </select>  -->
        <!-- <a href="javascript:void(0)" id="Luckyexcel-downlod-file">Download source xlsx file</a>
        <button class="excelexport">下载excel</button> -->
    </p>
    <div id="luckysheet" style="height: 92%; margin:0px;padding:0px;position:absolute;width:100%;left: 0px;top: 0;bottom: 0px;">
    </div>
    
    <script src="luckyexcel.js"></script>
    <!-- <script type="module" src="index.js"></script> -->
    <!-- <script type="module">
            // import {importFile} from './main';
            window.onload = () => {
                let upload = document.getElementById("file");
                upload.addEventListener("change", function(evt){
                    var files = evt.target.files;   
                    importFile(files[0]);
                });
            }
        </script> -->
    <script>
        let selectADemo = document.getElementById("Luckyexcel-demo-file");
        
        // let exportBtn = document.querySelector('.excelexport')
       
        function importExcel (){
            selectADemo.click()
        }
        function changeValue(val) {
            selectADemo.name="test"
            selectADemo.value = val 
        }
        function exportHtml(){
            let allSheetData = luckysheet.getluckysheetfile();
            allSheetData.forEach(item=>{
                exportFile(item,"html")
            })
        }
        function exportFile(sheet,type) {
            
            let sheet1 = sheet;
            let downOriginData = sheet1.data;
            // 该方法会返回一个数组结构，其中包含了我们所创建的所有的sheet数据
            // allSheetData = [{sheet1},{sheet2},{sheet3}]
            let arr = [];  // 所有的单元格数据组成的二维数组
            let bgConfig = {};
            let percentageReg = /%$/;
            let cellValue = null;
            // 获取单元格的背景色
            // function setBackground(row, col, bg) {
            //     var colA = luckysheet.luckysheetchatatABC(col);
            //     var key = colA + (row + 1);
            //     bgConfig[key] = bg.replace(/\#?/, '');
            // }

            // 判断值类型是否为百分比 %
            function isPercentage(value) {
                return percentageReg.test(value.m) && value.ct && value.ct.t === 'n'
            }

            // 获取二维数组
            for (let row = 0; row < downOriginData.length; row++) {
                let arrRow = [];
                for (let col = 0; col < downOriginData[row].length; col++) {
                    if (cellValue = downOriginData[row][col]) {
                        // 处理单元格的背景颜色
                        // if (cellValue.bg) {
                        //     setBackground(row, col, cellValue.bg)
                        // }
                        if (cellValue.ct != null && cellValue.ct.t == 'd') {
                            //  d为时间格式  2019-01-01   或者2019-01-01 10:10:10
                            arrRow.push(new Date(cellValue.m.replace(/\-/g, '/'))) //兼容IE
                        } else if (cellValue.m && isPercentage(cellValue)) {
                            //百分比问题
                            arrRow.push(cellValue.m)
                        } else {
                            arrRow.push(cellValue.v)
                        }
                    }
                }
                arr.push(arrRow)
            }
            let opts = {
                dateNF: 'm/d/yy h:mm',
                cellDates: true,
                cellStyles: true
            }
            let ws = XLSX.utils.aoa_to_sheet(arr, opts)
            //设置单元格的类型
            let reg = /[\u4e00-\u9fa5]/g;
            for (let key in ws) {
                let item = ws[key]
                if (item.t === 'd') {
                    if (item.w) {
                        //时间格式的设置
                        let arr = item.w.split(' ')
                        if (arr[1] && arr[1] == '0:00') {
                            ws[key].z = 'm/d/yy'
                        } else {
                            item.z = 'yyyy/m/d h:mm:ss'
                        }
                    }
                } else if (item.t === 's') {
                    //百分比设置格式
                    if (item.v && !item.v.match(reg) && item.v.indexOf('%') > -1) {
                        item.t = 'n'
                        item.z = '0.00%'
                        item.v = Number.parseFloat(item.v) / 100
                    }
                    else if (item.v && item.v.match(reg)) {
                        //含有中文的设置居中样式
                        item['s'] = {
                            alignment: { vertical: 'center', horizontal: 'center' }
                        }
                    }
                }
                // 设置单元格样式
                if (bgConfig[key]) {
                    ws[key]['s'] = {
                        alignment: { vertical: 'center', horizontal: 'center' },
                        fill: { bgColor: { indexed: 32 }, fgColor: { rgb: bgConfig[key] } },
                        border: {
                            top: { style: 'thin', color: { rgb: '999999' } },
                            bottom: { style: 'thin', color: { rgb: '999999' } },
                            left: { style: 'thin', color: { rgb: '999999' } },
                            right: { style: 'thin', color: { rgb: '999999' } }
                        }
                    }
                }
            }
            //组装下载数据格式
            let name = sheet1.name;
            let tmpWB = {
                SheetNames: [name], //保存的表标题
                Sheets: {
                    [name]: Object.assign({}, ws)//内容 
                }
            }
            let mergeArr = [];
            if(sheet1.config.merge) {
                let mergeConfig = sheet1.config.merge
                if (JSON.stringify(mergeConfig) !== '{}') {
                mergeArr = handleMergeData(mergeConfig)
                tmpWB.Sheets[name]['!merges'] = mergeArr
            }
            }
           
            
            //处理合并单元格config数据
            function handleMergeData(origin) {

                let result = []
                if (origin instanceof Object) {
                    var r = "r",
                        c = "c",
                        cs = "cs",
                        rs = "rs";
                    for (var key in origin) {
                        var startR = origin[key][r];
                        var endR = origin[key][r];
                        var startC = origin[key][c];
                        var endC = origin[key][c];

                        // 如果只占一行 为1 如果占两行 为2
                        if (origin[key][cs] > 0) {
                            endC = startC + (origin[key][cs] - 1);
                        }
                        if (origin[key][rs] > 0) {
                            endR = startR + (origin[key][rs] - 1);
                        }
                        // s为合并单元格的开始坐标  e为结束坐标
                        var obj = { s: { "r": startR, "c": startC }, e: { "r": endR, "c": endC } }
                        result.push(obj)
                    }
                }
                return result
            }
            // sheetjs js-xlsx 的方法 ，不能设置单元格格式
            XLSX.writeFile(tmpWB, sheet.name + `.${type}`);
        }
        // exportBtn.addEventListener("click", exportFile)
        //导出Excel
        function expFile() {
            let allSheetData = luckysheet.getLuckysheetfile();
            exportExcel(allSheetData, ExcelJS)
            function exportExcel(luckysheet, Excel) { // 参数为luckysheet.getluckysheetfile()获取的对象
                // 1.创建工作簿，可以为工作簿添加属性
                const workbook = new Excel.Workbook()
                // 2.创建表格，第二个参数可以配置创建什么样的工作表
                luckysheet.every(function (table) {
                    if (table.data.length === 0) return true
                    const worksheet = workbook.addWorksheet(table.name,{views: [{showGridLines: true}]})
                    // 3.设置单元格合并,设置单元格边框,设置单元格样式,设置值
                    setStyleAndValue(table.data, worksheet)
                    setMerge(table.config.merge, worksheet)
                    // setBorder(table.config.borderInfo, worksheet)
                    return true
                })
                // 4.写入 buffer
                const EXCEL_TYPE = 'application/vnd.openxmlformats-officedocument.spreadsheetml.sheet;charset=UTF-8'
                workbook.xlsx.writeBuffer().then((data => {
                    const blob = new Blob([data], { type: EXCEL_TYPE })
                    saveAs(blob, 'download.xlsx')
                }))
            }

            function setMerge(luckyMerge = {}, worksheet) {
                const mergearr = Object.values(luckyMerge)
                mergearr.forEach(function (elem) { // elem格式：{r: 0, c: 0, rs: 1, cs: 2}
                    // 按开始行，开始列，结束行，结束列合并（相当于 K10:M12）
                    worksheet.mergeCells(elem.r + 1, elem.c + 1, elem.r + elem.rs, elem.c + elem.cs)
                })
            }

            // function setBorder(luckyBorderInfo, worksheet) {
            //     if (!Array.isArray(luckyBorderInfo)) return
            //     luckyBorderInfo.forEach(function (elem) {
            //         let border = borderConvert(elem.borderType, elem.style, elem.color)
            //         if(elem.rangeType === "range") {
            //             let rang = elem.range[0]
            //             console.log("rang.column_focus + 1, rang.row_focus + 1")
            //          console.log(rang.column_focus + 1, rang.row_focus + 1)
            //             worksheet.getCell(rang.row_focus + 1, rang.column_focus + 1).border = border
            //         }else{
            //             worksheet.getCell((elem.value.row_index, elem.value.row_index).border = border)
            //         }
                    
                     
                   
            //     })
            // }
            function setStyleAndValue(cellArr, worksheet) {
                if (!Array.isArray(cellArr)) return
                cellArr.forEach(function (row, rowid) {
                    row.every(function (cell, columnid) {
                        if (!cell) return true
                        // let fill = fillConvert(cell.bg)
                        let font = fontConvert(cell.ff, cell.fc, cell.bl, cell.it, cell.fs, cell.cl, cell.ul)
                        let alignment = alignmentConvert(cell.vt, cell.ht, cell.tb, cell.tr)
                        let value
                        if (cell.f) {
                            value = { formula: cell.f, result: cell.v }
                        } else {
                            value = cell.v
                        }
                        let target = worksheet.getCell(rowid + 1, columnid + 1)
                        // target.fill = fill
                        target.font = font
                        target.alignment = alignment
                        target.value = value
                        return true
                    })
                })
            }

            // function fillConvert(bg) {
                
            //     if (!bg) {
            //         return {}
            //     }
            //     let fill = {
            //         type: 'pattern',
            //         pattern: 'solid',
            //         fgColor: { argb: bg.indexOf("#") == -1 ? 'ffffff' : bg.replace('#', '') }
            //     }
            //     return fill
            // }

            function fontConvert(ff = 0, fc = '#000000', bl = 0, it = 0, fs = 10, cl = 0, ul = 0) { // luckysheet：ff(样式), fc(颜色), bl(粗体), it(斜体), fs(大小), cl(删除线), ul(下划线)
                const luckyToExcel = {
                    0: '微软雅黑',
                    1: '宋体（Song）',
                    2: '黑体（ST Heiti）',
                    3: '楷体（ST Kaiti）',
                    4: '仿宋（ST FangSong）',
                    5: '新宋体（ST Song）',
                    6: '华文新魏',
                    7: '华文行楷',
                    8: '华文隶书',
                    9: 'Arial',
                    10: 'Times New Roman ',
                    11: 'Tahoma ',
                    12: 'Verdana',
                    num2bl: function (num) {
                        return num === 0 ? false : true
                    }
                }

                let font = {
                    name: luckyToExcel[ff],
                    family: 1,
                    size: fs,
                    color: { argb: fc.replace('#', '') },
                    bold: luckyToExcel.num2bl(bl),
                    italic: luckyToExcel.num2bl(it),
                    underline: luckyToExcel.num2bl(ul),
                    strike: luckyToExcel.num2bl(cl)
                }

                return font
            }

            function alignmentConvert(vt = 'default', ht = 'default', tb = 'default', tr = 'default') { // luckysheet:vt(垂直), ht(水平), tb(换行), tr(旋转)
                const luckyToExcel = {
                    vertical: {
                        0: 'middle',
                        1: 'top',
                        2: 'bottom',
                        default: 'top'
                    },
                    horizontal: {
                        0: 'center',
                        1: 'left',
                        2: 'right',
                        default: 'left'
                    },
                    wrapText: {
                        0: false,
                        1: false,
                        2: true,
                        default: false
                    },
                    textRotation: {
                        0: 0,
                        1: 45,
                        2: -45,
                        3: 'vertical',
                        4: 90,
                        5: -90,
                        default: 0
                    }
                }

                let alignment = {
                    vertical: luckyToExcel.vertical[vt],
                    horizontal: luckyToExcel.horizontal[ht],
                    wrapText: luckyToExcel.wrapText[tb],
                    textRotation: luckyToExcel.textRotation[tr]
                }
                return alignment

            }

            function borderConvert(borderType, style = 1, color = '#000000') { // 对应luckysheet的config中borderinfo的的参数
                if (!borderType) {
                    return {}
                }
                const luckyToExcel = {
                    type: {
                        'border-all': 'all',
                        'border-top': 'top',
                        'border-right': 'right',
                        'border-bottom': 'bottom',
                        'border-left': 'left'
                    },
                    style: {
                        0: 'none',
                        1: 'thin',
                        2: 'hair',
                        3: 'dotted',
                        4: 'dashDot', // 'Dashed',
                        5: 'dashDot',
                        6: 'dashDotDot',
                        7: 'double',
                        8: 'medium',
                        9: 'mediumDashed',
                        10: 'mediumDashDot',
                        11: 'mediumDashDotDot',
                        12: 'slantDashDot',
                        13: 'thick'
                    }
                }
                let template = { style: luckyToExcel.style[style], color: { argb: color.replace('#', '') } }
                let border = {}
                if (luckyToExcel.type[borderType] === 'all') {
                    border['top'] = template
                    border['right'] = template
                    border['bottom'] = template
                    border['left'] = template
                } else {
                    border[luckyToExcel.type[borderType]] = template
                }
                return border
            }
        }
            
    </script>
</body>

</html>